﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


using System.Data;
using System.Data.OleDb;

public partial class add_receipt : System.Web.UI.Page
{
    OleDbConnection conx;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Back_Click(object sender, EventArgs e)
    {
        Response.Redirect("receipt.aspx");
    }
    protected void Add_Order_Click(object sender, EventArgs e)
    {
        try
        {
            String con = "Provider=MSDAORA;Data Source=orcl;Password=oracle;User ID=pearl";//"DSN=orcl;Driver={Microsoft ODBC for Oracle};UID=system;PWD=password;SERVER=localhost;";
            conx = new OleDbConnection(con);
            conx.Open();



            if (conx.State == ConnectionState.Open)
            {
                string pk = "";
                foreach (GridViewRow row in GridView1.Rows)
                {
                    CheckBox cbd = (CheckBox)row.FindControl("checkBoxAddOrder");
                    if (cbd != null && cbd.Checked)
                    {
                        pk = row.RowIndex.ToString();

                        // Select Counter ORDER_NO prepare to make order_header id
                        DataSet ds = new DataSet("Dataset1");
                        OleDbDataAdapter ODDA = new OleDbDataAdapter("SELECT AMOUNT FROM COUNTS WHERE NAME = 'ORDER_NO'", conx);
                        ODDA.Fill(ds, "MYDATA");
                        
                        string test = ds.Tables["MYDATA"].Rows[0]["AMOUNT"].ToString();
                        int order_detail_amt = Convert.ToInt32(test);


                        string order_detail_id = "";
                        if (order_detail_amt < 10)
                        {
                            order_detail_id = "R00000" + order_detail_amt;
                            //Label3.Text = reservation_id;
                        }
                        else if (order_detail_amt >= 10 && order_detail_amt <= 99)
                        {
                            order_detail_id = "R0000" + order_detail_amt;
                            //Label3.Text = reservation_id;
                        }
                        else if (order_detail_amt >= 100 && order_detail_amt <= 999)
                        {
                            order_detail_id = "R000" + order_detail_amt;
                            //Label3.Text = reservation_id;
                        }
                        else if (order_detail_amt >= 1000 && order_detail_amt <= 9999)
                        {
                            order_detail_id = "R00" + order_detail_amt;
                            //Label3.Text = reservation_id;
                        }
                        else if (order_detail_amt >= 10000 && order_detail_amt <= 99999)
                        {
                            order_detail_id = "R0" + order_detail_amt;
                            //Label3.Text = reservation_id;

                        }
                        else if (order_detail_amt >= 100000 && order_detail_amt <= 999999)
                        {
                            order_detail_id = "R" + order_detail_amt;
                            //Label3.Text = reservation_id;
                        }

                        
                        








                        /*
                        // INSERT ORDER_DETAIL
                        OleDbCommand ODC = new OleDbCommand("INSERT INTO ORDER_DETAIL VALUES ('4','R000003','1701','4','4000');", conx); //Insert Command
                        ODC.ExecuteNonQuery();
                        */

                        
                        DataSet ds_reservations = new DataSet("reservations_dataset");
                        OleDbDataAdapter ODDA_reservations
                        =
                        new OleDbDataAdapter(@"SELECT        ROOM_DETAIL.PRICE, ROOM_DETAIL.ROOM_TYPE_NAME, RESERVATIONS.ID, RESERVATIONS.GUEST_ID, RESERVATIONS.ROOM_NO, 
                         RESERVATIONS.STAFF_ID
FROM            RESERVATIONS INNER JOIN
                         ROOMS ON RESERVATIONS.ROOM_NO = ROOMS.ROOM_NO INNER JOIN
                         ROOM_DETAIL ON ROOMS.ROOM_TYPE_ID = ROOM_DETAIL.ROOM_TYPE_ID
WHERE        (RESERVATIONS.STATUS = 'Y')", conx);
                        ODDA.Fill(ds_reservations, "reservations_source");


                        test = ds_reservations.Tables["reservations_source"].Rows[0]["ROOM_DETAIL.PRICE"].ToString();
                        int price = Convert.ToInt32(test);
                        price = price * Convert.ToInt32(Convert.ToDateTime(GridView1.Rows[row.RowIndex].Cells[5].Text) - Convert.ToDateTime(GridView1.Rows[row.RowIndex].Cells[6].Text));

                        


                        if (row.RowIndex < GridView1.Rows.Count)
                        if (!GridView1.Rows[row.RowIndex].Cells[2].Text.Equals(GridView1.Rows[row.RowIndex+1].Cells[2].Text))
                        {
                        // FLAG
                        //INSERT INTO ORDER_HEADER VALUES ('R000002','G110002','S002','3000','07-JUL-2011');
                            OleDbCommand ODC = new OleDbCommand("INSERT INTO ORDER_HEADER VALUES ('" + order_detail_id + "','" + GridView1.Rows[row.RowIndex].Cells[2].Text + "','" + GridView1.Rows[row.RowIndex].Cells[4].Text + "'," + price + ",'" + DateTime.Now.ToString("dd-MMM-yyyy") + "')", conx); /*Insert Command*/
                            ODC.ExecuteNonQuery();
                        
                        }

                        
                        

                        
                        // order_detail
                        order_detail_amt = order_detail_amt + 1;
                        OleDbCommand ODC_update = new OleDbCommand("UPDATE COUNTS SET AMOUNT = " + order_detail_amt + " WHERE NAME = 'ORDER_NO'", conx);
                        ODC_update.ExecuteNonQuery();
                        



                        Label1.Text = "\"New Order Inserted\"";
                    }
                }
                GridView1.DataBind();

            }
            else
                Label1.Text = "Oracle Connect Failed";
        }

        catch (Exception ex)
        {
            Label1.Text = ex.Message + "<br />" + ex.StackTrace;
        }

        finally
        {
            conx.Close();
            conx = null;
        }
    }
}